/* 
 * This is all the sql code for the table of the users
 * =====================================================
*/

CREATE SEQUENCE   "USERS_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  ORDER  NOCYCLE
/


CREATE TABLE  "USERS" 
   (	"ID" NUMBER NOT NULL ENABLE, 
	"USERNAME" VARCHAR2(32) NOT NULL ENABLE, 
	"EMAIL" VARCHAR2(32), 
	"NAME" VARCHAR2(32), 
	"PASSWORD" VARCHAR2(32) NOT NULL ENABLE, 
	 CONSTRAINT "USERS_PK" PRIMARY KEY ("ID") ENABLE
   )
/


CREATE OR REPLACE TRIGGER  "BI_USERS" 
  before insert on "USERS"               
  for each row  
begin   
    select "USERS_SEQ".nextval into :NEW.ID from dual; 
end; 

/
ALTER TRIGGER  "BI_USERS" ENABLE
/

/*
 * This is the sql code for the genre
 * ====================================
*/

CREATE SEQUENCE   "GENRES_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  ORDER  NOCYCLE
/

CREATE TABLE  "GENRES" 
   (	"ID" NUMBER NOT NULL ENABLE, 
	"NAME" VARCHAR2(255) NOT NULL ENABLE, 
	 CONSTRAINT "GENRES_PK" PRIMARY KEY ("ID") ENABLE
   )
/


CREATE OR REPLACE TRIGGER  "BI_GENRES" 
  before insert on "GENRES"               
  for each row  
begin   
    select "GENRES_SEQ".nextval into :NEW.ID from dual; 
end; 

/
ALTER TRIGGER  "BI_GENRES" ENABLE
/

/*
 * The sql for the artists
 * =========================
*/

CREATE SEQUENCE   "ARTISTS_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  ORDER  NOCYCLE
/

CREATE TABLE  "ARTISTS" 
   (	"ID" NUMBER NOT NULL ENABLE, 
	"NAME" VARCHAR2(255) NOT NULL ENABLE, 
	 CONSTRAINT "ARTISTS_PK" PRIMARY KEY ("ID") ENABLE
   )
/


CREATE OR REPLACE TRIGGER  "BI_ARTISTS" 
  before insert on "ARTISTS"               
  for each row  
begin   
    select "ARTISTS_SEQ".nextval into :NEW.ID from dual; 
end; 

/
ALTER TRIGGER  "BI_ARTISTS" ENABLE
/

/*
 * The sql for the discs
 * ==========================
*/

--CREATE SEQUENCE   "DISCS_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  ORDER  NOCYCLE
--/

CREATE TABLE  "DISCS" 
   (	"ID" NUMBER NOT NULL ENABLE, 
	"SUB_GENRE_ID" NUMBER NOT NULL ENABLE, 
	"NAME" VARCHAR2(255) NOT NULL ENABLE, 
	 CONSTRAINT "DISCS_PK" PRIMARY KEY ("ID") ENABLE, 
	 CONSTRAINT "DISCS_FK" FOREIGN KEY ("SUB_GENRE_ID")
	  REFERENCES  "SUB_GENRES" ("ID") ENABLE
   )
/


--CREATE OR REPLACE TRIGGER  "BI_DISCS" 
-- before insert on "DISCS"               
--  for each row  
--begin   
--    select "DISCS_SEQ".nextval into :NEW.ID from dual; 
--end; 

--/
--ALTER TRIGGER  "BI_DISCS" ENABLE
--/

/*
 * The sql for the tracks
 * ========================
*/

CREATE SEQUENCE   "TRACKS_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  ORDER  NOCYCLE
/

CREATE TABLE  "TRACKS" 
   (	"ID" NUMBER NOT NULL ENABLE, 
	"ARTIST_ID" NUMBER NOT NULL ENABLE, 
	"DISC_ID" NUMBER NOT NULL ENABLE, 
	"NAME" VARCHAR2(255) NOT NULL ENABLE, 
	"ORDER_NUM" NUMBER NOT NULL ENABLE, 
	"URL" VARCHAR2(255), 
	 CONSTRAINT "TRACKS_PK" PRIMARY KEY ("ID") ENABLE, 
	 CONSTRAINT "TRACKS_FK_ARTIST" FOREIGN KEY ("ARTIST_ID")
	  REFERENCES  "ARTISTS" ("ID") ENABLE, 
	 CONSTRAINT "TRACKS_FK_DISC" FOREIGN KEY ("DISC_ID")
	  REFERENCES  "DISCS" ("ID") ENABLE
   )
/


CREATE OR REPLACE TRIGGER  "BI_TRACKS" 
  before insert on "TRACKS"               
  for each row  
begin   
    select "TRACKS_SEQ".nextval into :NEW.ID from dual; 
end; 

/
ALTER TRIGGER  "BI_TRACKS" ENABLE
/

/*
 * The sql for the sub genres
 * ==============================
*/

CREATE sequence "SUB_GENRES_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  ORDER  NOCYCLE
/

CREATE table "SUB_GENRES" (
    "ID"         NUMBER NOT NULL,
    "GENRE_ID"   NUMBER NOT NULL,
    "NAME"       VARCHAR2(255) NOT NULL,
    constraint  "SUB_GENRES_PK" primary key ("ID")
)
/

CREATE trigger "BI_SUB_GENRES"  
  before insert on "SUB_GENRES"              
  for each row 
begin  
    select "SUB_GENRES_SEQ".nextval into :NEW.ID from dual;
end;
/   

ALTER TABLE "SUB_GENRES" ADD CONSTRAINT "SUB_GENRES_FK" 
FOREIGN KEY ("GENRE_ID")
REFERENCES "GENRES" ("ID")

/

/*
 * The sql for the indexes 
 *==========================
*/

create index "SUB_GENRES_IDX1"
 on "SUB_GENRES" ("GENRE_ID")
/   

create index "DISCS_IDX1"
 on "DISCS" ("SUB_GENRE_ID")
/   

create index "TRACKS_IDX1"
 on "TRACKS" ("ARTIST_ID")
/   

create index "TRACKS_IDX2"
 on "TRACKS" ("DISC_ID")
/   

			

  CREATE TABLE "IMUSIC"."ARTIST2USER" 
   (	"ARTIST_ID" NUMBER, 
	"USER_ID" NUMBER, 
	"RATING" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "CS_TS" ;
 

 
  CREATE TABLE "IMUSIC"."GENRE2USER" 
   (	"GENRE_ID" NUMBER, 
	"USER_ID" NUMBER, 
	"RATING" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "CS_TS" ;

/*
 * More indexes
 * ==============
*/ 
  
create index "TRACKS_IDX3"
 on "TRACKS" ("NAME")
/   

create index "ARTISTS_IDX1"
 on "ARTISTS" ("NAME")
/   

create index "DISCS_IDX2"
 on "DISCS" ("NAME")
/   

create index "SUB_GENRES_IDX2"
 on "SUB_GENRES" ("NAME")
/   






   